﻿//HocsinhDAO.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DTO;
using DataAccess;

namespace DAO
{
    public class HocsinhDAO
    {
        public static HocsinhDTO SelectById(string Mahs)
        {
            HocsinhDTO kq;
            kq = new HocsinhDTO();
            DataTable dt = MsAccess.ExecuteQuery(string.Format("SELECT * FROM HOCSINH WHERE '{0}'=MAHS", Mahs));
            if (dt.Rows.Count == 0)
                throw new Exception("No item");
            kq.Mahs = dt.Rows[0]["MAHS"].ToString();
            kq.Holot = dt.Rows[0]["HOLOT"].ToString();
            kq.Ten = dt.Rows[0]["TEN"].ToString();
            kq.Ngaysinh = DateTime.Parse(dt.Rows[0]["NGAYSINH"].ToString());
            kq.Malop = dt.Rows[0]["MALOP"].ToString();
            return kq;
        }
        public static List<HocsinhDTO> SelectAll()
        {
            List<HocsinhDTO> kq = new List<HocsinhDTO>();
            DataTable dt = MsAccess.ExecuteQuery("SELECT * FROM HOCSINH");
            HocsinhDTO tam;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tam = new HocsinhDTO();
                tam.Mahs = dt.Rows[i]["MAHS"].ToString();
                tam.Holot = dt.Rows[i]["HOLOT"].ToString();
                tam.Ten = dt.Rows[i]["TEN"].ToString();
                tam.Ngaysinh = DateTime.Parse(dt.Rows[i]["NGAYSINH"].ToString());
                tam.Malop = dt.Rows[i]["MALOP"].ToString();
                kq.Add(tam);
            }
            return kq;
        }
        public static void AddNew(HocsinhDTO hs)
        {
            string sql=string.Format("INSERT INTO HOCSINH(MAHS,HOLOT,TEN,NGAYSINH,MALOP) VALUES('{0}','{1}','{2}','{3}','{4}')",hs.Mahs,hs.Holot,hs.Ten,hs.Ngaysinh,hs.Malop);
            MsAccess.ExecuteNonQuery(sql);
        }
        public static void Delete(HocsinhDTO hs)
        {
            string sql = string.Format("DELETE FROM HOCSINH WHERE '{0}' = MAHS ;", hs.Mahs);
            MsAccess.ExecuteNonQuery(sql);
        }
        public static void Update(HocsinhDTO hs)
        {
            string set = "";
            set += "HOLOT = '" + hs.Holot + "',";
            set += "TEN = '" + hs.Ten + "',";
            set += "NGAYSINH = '" + hs.Ngaysinh + "',";
            set += "MALOP = '" + hs.Malop + "'";
            string sql = string.Format("UPDATE HOCSINH SET {0} WHERE '{1}' = MAHS ;",set,hs.Mahs);
            MsAccess.ExecuteNonQuery(sql);
        }
    }
}
